package com.wuseguang.springcloud.service.impl;


import com.wuseguang.springcloud.Classcardservice.ClassCardService;
import com.wuseguang.springcloud.Filter.RequestParameterWrapper;
import com.wuseguang.springcloud.dao.jdbc.SchoolManagementJdbc;
import com.wuseguang.springcloud.service.SchoolManagementService;
import com.wuseguang.springcloud.util.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class SchoolManagementServiceImpl implements SchoolManagementService {

    private Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private SchoolManagementJdbc managementJdbc;


    //推送接口
    @Autowired
    private ClassCardService classCardService;

    /**
     * @描述 根据权限查询数据
     * @创建人 zm
     * @创建时间 2020/5/4
     */
    @Override
    public Result queryPersonDataByRole(Integer role) {
        Map<String, Object> personMap = new HashMap<>();
        String sql = "SELECT id,CONCAT(corporate_name,'-',admin_name) name,phone,head_portrait FROM school_admin WHERE role=?";
        String sql1 = "SELECT id,corporate_name name,phone,head_portrait FROM school_admin WHERE role=?";
        if (role == 1) {//管理员
            personMap.put("adminList", managementJdbc.queryListMap(sql1, 1));
            personMap.put("operateList", managementJdbc.queryListMap(sql, 2));
            personMap.put("partnerList", managementJdbc.queryListMap(sql1, 3));
        }
        if (role == 2) {//运营人员
            personMap.put("partnerList", managementJdbc.queryListMap(sql1, 3));
        }
        return new Result(personMap);
    }

    /**
     * @描述 运营人员添加修改数据/添加
     * @创建人 zm
     * @创建时间 2020/5/4
     */
    @Override
    public Result addPersonData(Integer id, String head_portrait, String admin_name, String phone, String password) {
        String sql = "insert into `school_admin` (`admin_name`, `phone`, `password`, `role`, `head_portrait`, `opertime`) values(?,?,?,?,?,now())";
        if (id == null || id == 0) {
            int i = managementJdbc.updateByParameter(sql, admin_name, phone, MyDES.encryptBasedDes(password), 2, head_portrait);
            if (i > 0) {
                return new Result();
            }
        } else {
            sql = "UPDATE school_admin SET admin_name=?,phone=?,password=?,head_portrait=?,opertime=now() where id=?";
            int i = managementJdbc.updateByParameter(sql, admin_name, phone, MyDES.encryptBasedDes(password), head_portrait, id);
            if (i > 0) {
                return new Result();
            }
        }
        return new Result(CodeUtil.MODIFICATION_FAILED);
    }


    /**
     * @描述 根据id查个人信息
     * @创建人 zm
     * @创建时间 2020/5/4
     */
    @Override
    public Result queryPersonDataById(Integer id) {
        String sql = "SELECT id,corporate_name,admin_name,phone,password,role,head_portrait,address,opertime FROM school_admin WHERE id=?";
        Map<String, Object> map = managementJdbc.queryMap(sql, id);
        map.put("password", MyDES.decryptBasedDes(map.get("password").toString()));
        return new Result(map);
    }

    /**
     * @描述 添加/修改代理商信息
     * @创建人 zm
     * @创建时间 2020/5/4
     */
    @Override
    public Result addAgentData(Integer id, String head_portrait, String corporate_name, String address, String phone, String password, String admin_name) {
        String sql = "insert into `school_admin` (`corporate_name`, `phone`, `password`, `role`, `head_portrait`,`address`, `opertime`,`admin_name`) values(?,?,?,?,?,?,now(),?)";
        if (id == null || id == 0) {
            int i = managementJdbc.updateByParameter(sql, corporate_name, phone, MyDES.encryptBasedDes(password), 3, head_portrait, address,admin_name);
            if (i > 0) {
                return new Result();
            }
        } else {
            sql = "UPDATE school_admin SET corporate_name=?,phone=?,password=?,head_portrait=?,address=?,opertime=now(),admin_name = ? where id=?";
            int i = managementJdbc.updateByParameter(sql, corporate_name, phone, MyDES.encryptBasedDes(password), head_portrait, address,admin_name, id);
            if (i > 0) {
                return new Result();
            }
        }
        return new Result(CodeUtil.MODIFICATION_FAILED);
    }

    /**
     * @描述 删除人员信息
     * @创建人 zm
     * @创建时间 2020/5/4
     */
    @Override
    public Result delPersonData(Integer id) {
        String sql = "DELETE FROM school_admin WHERE id=?";
        int i = managementJdbc.updateByParameter(sql, id);
        if (i > 0) {
            return new Result();
        }
        return new Result(CodeUtil.DELETE_FAILED);
    }


    /**
     * @描述 查所有的省->市->区
     * @创建人 zm
     * @创建时间 2020/5/4
     */
    @Override
    public Result queryDistrict(Integer id, Integer type) {
        if (StringUtils.isEmpty(type) || type == 0) {
            String sql = "SELECT id,pname name,1 type FROM province";
            List<Map<String, Object>> list = managementJdbc.queryListMap(sql);//省
            return new Result(list);
        }

        if (type == 1) {
            String sql = "SELECT  id,cname name,2 type FROM city where pid=?";
            List<Map<String, Object>> list = managementJdbc.queryListMap(sql, id);
            return new Result(list);
        }

        if (type == 2) {
            String sql = "SELECT id,dname name,3 type FROM district WHERE cid=?";
            List<Map<String, Object>> list = managementJdbc.queryListMap(sql, id);
            return new Result(list);
        }

        return new Result();
    }

    /**
     * @描述 查所有的学校
     * @创建人 zm
     * @创建时间 2020/5/4
     */
    @Override
    public Result querySchoolAll(String name, Integer id, Integer type, Integer indexNo, Integer rows, String adminId, String role, String corporateName) {
        String s = "";
        if ("3".equals(role)) {
            s = " AND s.school_admin_id=" + adminId;
        }
        if (id != null && id != 0) {
            if (type == 1) {
                s += " AND s.district_id in (" + managementJdbc.queryString("SELECT  GROUP_CONCAT(t.id) string FROM province p, city c,district t WHERE p.id=c.pid AND t.cid=c.id AND p.id=?", id) + " )";
            }
            if (type == 2) {
                s += " AND s.district_id in (" + managementJdbc.queryString("SELECT  GROUP_CONCAT(t.id) string FROM city c,district t WHERE  t.cid=c.id AND c.id=?", id) + " )";
            }

            if (type == 3) {
                s = " AND s.district_id =" + id;
            }
        }
        if (!StringUtils.isEmpty(name)) {
            s += " AND s.school_name like '%" + name + "%'";
        }

        if (!StringUtils.isEmpty(corporateName)) {
            s += " AND sa.corporate_name like '%" + name + "%'";
        }

        String sql = "SELECT s.id,s.school_name,(SELECT  CONCAT(p.pname,cname,dname)  FROM province p, city c,district t WHERE p.id=c.pid AND t.cid=c.id AND t.id=s.district_id )   address ,t.phone,t.password,sa.corporate_name  FROM school s LEFT JOIN teacher t ON s.id=t.school_id and t.admin=0 LEFT JOIN school_admin sa on(s.school_admin_id=sa.id) WHERE  1=1 " + s + " GROUP BY s.id";
        List<Map<String, Object>> list = managementJdbc.queryListMap(sql);

        for (int i = 0; i < list.size(); i++) {
            Map<String, Object> map = list.get(i);
            map.put("password", map.get("password") != null ? MyDES.decryptBasedDes(map.get("password").toString()) : "");
        }
//创    建分页工具
        PagingTool tool = new PagingTool();
        //放入页码
        tool.setIndexNo(indexNo);
        //放入每页显示数量
        tool.setRows(rows);
        //放入角色集合
        tool.setList(list);
        return new Result(new PaginationUtil<>(list.size(), rows, tool.getList(), indexNo));
    }


    /**
     * @描述 修改学校信息
     * @创建人 zm
     * @创建时间 2020/5/5
     */
    @Transactional
    @Override
    public Result updateSchoolDate(Integer sid, String sname, String logo, Integer districtId, Integer tid, String tname, String phone, String password, String endtime, Integer sbnumber, Integer type, Integer areaPlatformId, Integer jingWeiDu) {
        String sql = "SELECT id ints FROM teacher WHERE phone=?";
        Integer integer = managementJdbc.queryInteger(sql, phone);
        if (tid != null && !tid.equals(integer)&&integer!=0) {
            return new Result(CodeUtil.DUPLICATE_ACCOUNT_NUMBER);
        }
        sql = "UPDATE school SET school_name=?,logo=?,district_id=?, sbnumber=?,type=? ,area_platform_id= ?,jing_wei_du = ? WHERE id=?";
        managementJdbc.updateByParameter(sql, sname, logo, districtId, sbnumber, type,areaPlatformId, jingWeiDu,sid);
        if (!StringUtils.isEmpty(tid)) {//有账号修改信息
            sql = "UPDATE teacher SET name=?,phone=?,password=?, end_time=? WHERE id=?";
            managementJdbc.updateByParameter(sql, tname, phone, MyDES.encryptBasedDes(password), endtime, tid);
        } else {//没有账号时为添加账号
            sql = "insert into teacher(name,phone,password,end_time,school_id) VALUES (?,?,?,?,?)";
            managementJdbc.updateByParameter(sql, tname, phone, MyDES.encryptBasedDes(password), endtime, sid);
        }
        return new Result();
    }

    /**
     * @描述 添加学校信息
     * @创建人 zm
     * @创建时间 2020/5/5
     */
    @Transactional
    @Override
    public Result addSchoolDate(String sname, String logo, Integer districtId, String tname, String phone, String password, String endtime, Integer sbnumber, String adminId, Integer type, Integer areaPlatformId, Integer jingWeiDu) {
        String sql = "SELECT COUNT(1) ints FROM teacher WHERE phone=?";
        Integer integer = managementJdbc.queryInteger(sql, phone);
        if (integer > 0) {
            return new Result(CodeUtil.DUPLICATE_ACCOUNT_NUMBER);
        }
        sql = "insert into  school(school_name,logo,district_id,sbnumber,school_admin_id,type,area_platform_id,jing_wei_du) VALUES (?,?,?,?,?,?,?,?)";
        Long aLong = managementJdbc.updateByParameterGetId(sql, sname, logo, districtId, sbnumber, adminId, type,areaPlatformId,jingWeiDu);
        sql = "insert into teacher(name,phone,password,end_time,school_id,admin) VALUES (?,?,?,?,?,0)";
        managementJdbc.updateByParameter(sql, tname, phone, MyDES.encryptBasedDes(password), endtime, aLong);
        return new Result();
    }

    /**
     * @描述 根据id查学校信息
     * @创建人 zm
     * @创建时间 2020/5/5
     */
    @Override
    public Result querySchoolDateById(Integer id) {
        String sql = "SELECT s.id sid,s.school_name,s.logo ,t.id tid,t.name,DATE_FORMAT(t.end_time,'%Y-%m-%d %H:%i:%s') end_time,s.sbnumber,(SELECT  CONCAT(p.pname,cname,dname)  FROM province p, city c,district t WHERE p.id=c.pid AND t.cid=c.id AND t.id=s.district_id )   addressName,(SELECT  CONCAT(p.id,',',c.id,',',t.id)  FROM province p, city c,district t WHERE p.id=c.pid AND t.cid=c.id AND t.id=s.district_id )   address,s.district_id  ,t.phone,t.password,s.type FROM school s LEFT " +
                " JOIN teacher t ON (s.id=t.school_id AND t.admin=0 ) WHERE  s.id=? GROUP BY s.id";
        Map<String, Object> map = managementJdbc.queryMap(sql, id);
        try {
            map.put("password", map.get("password") != null ? MyDES.decryptBasedDes(map.get("password").toString()) : "");
        } catch (Exception e) {

        }
        return new Result(map);
    }

    /**
     * @描述 根据学校id 删除学校信息
     * @创建人 zm
     * @创建时间 2020/5/14
     */
    @Transactional
    @Override
    public Result delSchoolDateById(Integer id) {
        String sql = "  DELETE FROM school WHERE id=?";
        int i = managementJdbc.updateByParameter(sql, id);
        if (i > 0) {
            sql = "DELETE FROM teacher WHERE school_id=?";
            managementJdbc.updateByParameter(sql, id);
            sql = "DELETE FROM school_jurisdiction WHERE school_id=?";
            managementJdbc.updateByParameter(sql, id);
            sql = "DELETE FROM classes WHERE school_id=?";
            managementJdbc.updateByParameter(sql, id);
            sql = "DELETE FROM grade WHERE school_id=?";
            managementJdbc.updateByParameter(sql, id);
            sql = "DELETE FROM student WHERE school_id=?";
            managementJdbc.updateByParameter(sql, id);
            return new Result();
        }
        return new Result(CodeUtil.DELETE_FAILED);
    }

    /**
     * @描述 查询该学校功能权限
     * @创建人 zm
     * @创建时间 2020/5/6
     */
    @Override
    public Result querySchoolManagementJurisdiction(Integer schoolId) {
        //查询功能权限
        String sql = " SELECT id,CONCAT( name,'(',LEFT(CONCAT(IF(papplet=0,'','家|'),IF(tapplet=0,'','校|'),IF(web=0,'','pc|'),IF(bancard=0,'',CONCAT('班',IF(type=0,'-首|','-个|')))),CHAR_LENGTH(CONCAT(IF(papplet=0,'','家|'),IF(tapplet=0,'','校|'),IF(web=0,'','pc|'),IF(bancard=0,'',CONCAT('班',IF(type=0,'-首|','-个|')))))-1  ),')') name,pid ,IF(COUNT(1)>1,1,0) AS state FROM ( " +
                "     SELECT  id ,name ,pid,papplet,tapplet,web,bancard,TYPE  FROM jurisdiction WHERE pid =? " +
                "   UNION ALL SELECT jurisdiction_id  id ,jurisdiction_name name,jurisdiction_pid pid,papplet,tapplet,web,bancard,type  FROM school_jurisdiction WHERE jurisdiction_pid =?  AND school_id=? " +
                "    ) jurisdiction GROUP BY id   ";

        logger.info("管理员查询功能权限 sql= {},参数1 = {}，参数2 = {},参数3 = {}", 0, schoolId, 0);

        List<Map<String, Object>> list = managementJdbc.queryListMap(sql, 0, 0, schoolId);

        //循环一级权限
        for (Map<String, Object> s : list) {
            List<Map<String, Object>> twoList = managementJdbc.queryListMap(sql, s.get("id"), s.get("id"), schoolId);
            s.put("list", twoList);
        }
        return new Result(list);
    }

    /**
     * @描述 添加菜单权限
     * @创建人 zm
     * @创建时间 2020/5/6
     */
    @Transactional
    @Override
    public Result addSchoolManagementJurisdiction(String menuOne, String menuTwo, Integer id) {
        String sql = "delete  from school_jurisdiction where school_id=?";
        managementJdbc.updateByParameter(sql, id);//刪除菜單
        if (!StringUtils.isEmpty(menuOne)) {
            sql = "INSERT INTO `school_jurisdiction` (`school_id`, `jurisdiction_id`, " +
                    "`jurisdiction_pid`, `jurisdiction_name`, `papplet`, `tapplet`, `web`, `bancard`, `type`, `sign`, `url`) " +
                    "SELECT ?,id,pid,NAME,papplet,tapplet,web ,bancard,TYPE,SIGN,url FROM  jurisdiction WHERE id in (" + menuOne + ")";
            managementJdbc.updateByParameter(sql, id);//一級菜單
        }

        if (!StringUtils.isEmpty(menuTwo)) {
            sql = "INSERT INTO `school_jurisdiction` (`school_id`, `jurisdiction_id`, " +
                    "`jurisdiction_pid`, `jurisdiction_name`, `papplet`, `tapplet`, `web`, `bancard`, `type`, `sign`, `url`) " +
                    "SELECT ?,id,pid,NAME,papplet,tapplet,web ,bancard,TYPE,SIGN,url FROM  jurisdiction WHERE id in (" + menuTwo + ")";
            managementJdbc.updateByParameter(sql, id);//二級菜單
            sql = "INSERT INTO `school_jurisdiction` (`school_id`, `jurisdiction_id`, " +
                    "`jurisdiction_pid`, `jurisdiction_name`, `papplet`, `tapplet`, `web`, `bancard`, `type`, `sign`, `url`) " +
                    "SELECT ?,id,pid,NAME,papplet,tapplet,web ,bancard,TYPE,SIGN,url FROM  jurisdiction WHERE pid in (" + menuTwo + ")";
            managementJdbc.updateByParameter(sql, id);//三級權限
        }
        return new Result();
    }

    /**
     * @描述 添加学期
     * @创建人 zm
     * @创建时间 2020/5/8
     */
    @Override
    public Result addSchoolSemester(String name, Integer schoolId, String oneTerm, String oneTime, String twoTerm, String twoTime) {
        String sql = "insert into `school_year` (`name`, `school_id`, `oneterm`, `onetime`, `twoterm`, `twotime`, `opertime`) values(?,?,?,?,?,?,now())";
        int i = managementJdbc.updateByParameter(sql, name, schoolId, oneTerm, oneTime, twoTerm, twoTime);
        if (i < 0) {//添加失败
            return new Result(CodeUtil.ADD_FAILURE);
        }
        return new Result();
    }

    /**
     * @描述 修改学年信息
     * @创建人 zm
     * @创建时间 2020/5/8
     */
    @Override
    public Result updateSchoolSemester(Integer id, String name, Integer schoolId, String oneTerm, String oneTime, String twoTerm, String twoTime) {
        String sql = "UPDATE school_year set `name`=?, `school_id`=?, `oneterm`=?, `onetime`=?, `twoterm`=?, `twotime`=?, `opertime`=now()  where id=?";
        int i = managementJdbc.updateByParameter(sql, name, schoolId, oneTerm, oneTime, twoTerm, twoTime, id);
        if (i < 0) {//修改失败
            return new Result(CodeUtil.MODIFICATION_FAILED);
        }
        return new Result();
    }


    /**
     * @描述 设置为当前学年
     * @创建人 zm
     * @创建时间 2020/5/8
     */
    @Transactional
    @Override
    public Result updateSemesterState(Integer id, Integer schoolId) {
        String sql = "UPDATE school_year set `state`=1,`opertime`=now()  where school_id=?";
        int i = managementJdbc.updateByParameter(sql, schoolId);
        if (i > 0) {
            sql = "UPDATE school_year set `state`=0,`opertime`=now()  where id=?";
            i = managementJdbc.updateByParameter(sql, id);
            if (i < 0) {
                return new Result(CodeUtil.MODIFICATION_FAILED);
            }
            return new Result();
        }
        return new Result(CodeUtil.MODIFICATION_FAILED);
    }

    /**
     * @描述 根据id查学年信息
     * @创建人 zm
     * @创建时间 2020/5/8
     */
    @Override
    public Result querySemesterById(Integer id) {
        String sql = "SELECT `id`,`name`, `school_id`, `state`, `oneterm`, `onetime`, `twoterm`, `twotime` FROM school_year WHERE id=?";
        return new Result(managementJdbc.queryMap(sql, id));
    }

    /**
     * @描述 根据学校id查学年信息
     * @创建人 zm
     * @创建时间 2020/5/8
     */
    @Override
    public Result querySemesterBySchoolId(Integer schoolId) {
        String sql = "SELECT `id`,`name`, `school_id`, `state`, `oneterm`, `onetime`, `twoterm`, `twotime` FROM school_year WHERE school_id=?";
        return new Result(managementJdbc.queryListMap(sql, schoolId));
    }


    @Override
    public Result querySemesterYearId(Integer schoolId) {
        String sql = "SELECT `id`,`name` FROM school_year WHERE school_id=? and state=1";
        return new Result(managementJdbc.queryListMap(sql, schoolId));
    }

    /**
     * @描述 组合查询学校学年
     * @创建人 zm
     * @创建时间 2020/5/8
     */
    @Override
    public Result querySemesterSchoolList(String name, Integer indexNo, Integer rows) {
        String s = "";
        if (!StringUtils.isEmpty(name)) {
            s = " AND s.school_name like '%" + name + "%'";
        }
        String sql = "SELECT r.`id`,r.`name`, r.`school_id`, s.school_name , r.`state`, r.`oneterm`, r.`onetime`, r.`twoterm`, r.`twotime` FROM school_year r,school s WHERE s.id=r.school_id " + s;
        List<Map<String, Object>> list = managementJdbc.queryListMap(sql);
        PagingTool tool = new PagingTool();
        //放入页码
        tool.setIndexNo(indexNo);
        //放入每页显示数量
        tool.setRows(rows);
        //放入角色集合
        tool.setList(list);
        return new Result(new PaginationUtil<>(list.size(), rows, tool.getList(), indexNo));
    }


    /**
     * @描述 升学年
     * @创建人 zm
     * @创建时间 2020/5/8
     */
    @Transactional
    @Override
    public Result updateSchoolUpgrade(Integer schoolId, Integer id) {
        //毕业班级
        String sql = "UPDATE classes SET graduation=1,graduation_time=NOW() WHERE grade_id IN (  SELECT d.id FROM grade d WHERE  d.school_id =? AND d.name='六年级' )";
        managementJdbc.updateByParameter(sql, schoolId);
        sql = "UPDATE classes SET graduation=1,graduation_time=NOW() WHERE grade_id IN (  SELECT d.id FROM grade d WHERE  d.school_id =? AND d.name='初三' )";
        managementJdbc.updateByParameter(sql, schoolId);
        sql = "UPDATE classes SET graduation=1,graduation_time=NOW() WHERE grade_id IN (  SELECT d.id FROM grade d WHERE  d.school_id =? AND d.name='高三' )";
        managementJdbc.updateByParameter(sql, schoolId);
        //班级加1
        sql = "UPDATE classes SET grade_id=grade_id+1,graduation_time=NOW() WHERE grade_id IN ( SELECT d.id FROM grade d WHERE  d.school_id =? AND d.name!='其他' AND d.name!='六年级'  AND d.name!='初三' AND d.name!='高三')";
        managementJdbc.updateByParameter(sql, schoolId);
      /*  UPDATE classes SET grade_id=grade_id-1,graduation_time=NOW() WHERE graduation=0 AND grade_id IN (
                SELECT d.id FROM grade d WHERE  d.school_id =1 AND d.name!='其他' )
        UPDATE classes SET graduation=0 WHERE school_id =1   学年回退 sql*/
        //修改毕业班级和教室绑定的班级
        sql = "UPDATE classroom SET classid=NULL WHERE classid IN ( SELECT id FROM classes WHERE school_id=? AND graduation=1)";
        managementJdbc.updateByParameter(sql, schoolId);
        //删除毕业班级对应教师的角色信息
        sql = "DELETE FROM teacher_role WHERE type=3 AND   pid IN (  SELECT id FROM classes WHERE school_id=? AND graduation=1)";
        managementJdbc.updateByParameter(sql, schoolId);
        //删除毕业班级考勤组信息
        sql = "DELETE FROM attendance_group WHERE id IN ( SELECT g.attend_group_id id FROM classes s,attendance_group_classid g WHERE g.class_id=s.id AND s.school_id=? AND s.graduation=1)";
        managementJdbc.updateByParameter(sql, schoolId);
        updateSemesterState(id, schoolId);
        return new Result();
    }


    @Override
    public Result upload(String schoolId, MultipartFile file, int type) {

        logger.info("上传文件/图片/人脸");

        //文件url
        String url = CallbackSample.saveFile(file, schoolId, type);
        logger.info("url:" + url);
        //判断文件是否上传失败
        if ("error".equals(url)) {
            return new Result(CodeUtil.UPLOAD_FAILURE);
        }
        return new Result((Object) url);
    }

    @Override
    public Result updateSchoolAdminPassword(Integer id, String password) {
        String sql = "UPDATE school_admin SET  password=? ,opertime=now() where id=?";
        try {
            managementJdbc.updateByParameter(sql , MyDES.encryptBasedDes(password) , id);
        }catch (Exception e){
            return new Result(CodeUtil.FAILED_TO_RESET_PASSWORD);
        }
        return new Result();
    }

    @Override
    public void findNotLoggedIn(HttpServletRequest request, HttpServletResponse response, Integer sid) {

        String sql = "select student_name ,c.`name`,p.phone,ss.school_name " +
                " from student s left join parent_student ps on(ps.student_id=s.id) " +
                " left join parent p on(p.id=ps.parent_id) " +
                " left JOIN classes c on(c.id = s.class_id) " +
                " left join school ss on(ss.id = s.school_id) " +
                " where p.open_id_one is null and s.school_id = ? ORDER BY class_id desc";

        //key
        String[] key = new String[]{"学生名字","班级名称","手机号码","学校名字"};
        //values
        String[] value = new String[]{"student_name","name","phone","school_name"};

        List<Map<String,Object>> list = managementJdbc.queryListMap(sql,sid);

        try {
            ExeclUtils.downloadExcelMapTwoTemplate(response, list, key, value, "家长未登录信息");
        } catch (IOException e) {
            logger.info("课表模板错误,e", e);
        }
    }


    /**
     * @Author zm 删除人脸数据推送
     * @Description
     * @Date 2020/10/20 15:07
     */
    @Override
    public Result delFaceDataByClassId(String classIds) {
        try {
            String sql = "UPDATE bancard SET  emfaceflag=1 where classroomid in  (SELECT id FROM classroom WHERE classid IN ("+classIds+"))";
            managementJdbc.updateByParameter(sql);
            classCardService.classesAndCards(classIds);
            return new Result();
        }catch (Exception e){

        }
        return new Result(CodeUtil.DELETE_FAILED);
    }

    @Override
    public Result queryClassList(Integer schoolId) {
        String sql = "SELECT id,name FROM classes WHERE school_id=?";
        List<Map<String, Object>> list = managementJdbc.queryListMap(sql, schoolId);
        return new Result(list);
    }
}
